<%@page import="com.DBtools"%>
<%@ page language="java" contentType="text/html; charset=UTF-8"
    pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<!DOCTYPE html>
<html>
<head>
 <%@ include file="../bootstrap.jsp"%>
<meta charset="UTF-8">
<title>数据查询页面</title>
</head>
<body>
<h4 align="center">数据查询</h4>
<hr>
 <style> 
 
 input{width:30px;}
 form{width:50%;}
 </style>
<%

request.setCharacterEncoding("UTF-8");
String DepName=(String) request.getSession().getAttribute("UserName");
//接收用户提交的查询数据
String datetime =request.getParameter("datetime");
System.out.println("datetime"+datetime);
String datetime2 =request.getParameter("datetime2");

String selt1 =request.getParameter("selt1");
String selt2 =request.getParameter("selt2");
System.out.println("selt1："+selt1+"和selt2："+selt2);

String testnum =request.getParameter("testnum");
String checknum =request.getParameter("checknum");
System.out.println("testnum："+testnum+"和checknum："+checknum);



String  date1=null;
String  date2=null;

String sql="";
ResultSet rs=null;

%>
<style> form{margin: 20px;}</style>


<form>
	
	  <div class="form-inline">
	 	 <div style="width: 40px;"></div>
			<div class="form-group col-mb-1"> <!-- form-group col-mb-1 -->
				<div class="input-group flex-nowrap">
					<div class="input-group-prepend">
						<span class="input-group-text" id="inputGroup-sizing-default">时间范围</span>
					</div>
					<input type="datetime-local" class="form-control" name="datetime" required="required"
						value="<%=datetime==null ? "":datetime %>"
						aria-label="Sizing example input"
						aria-describedby="inputGroup-sizing-default">
					 <input type="datetime-local" class="form-control" name="datetime2" required="required"
						value="<%=datetime2==null ? "":datetime2 %>"
						aria-label="Sizing example input"
						aria-describedby="inputGroup-sizing-default">

				</div>
			</div>
			
			  <div class="col-auto my-1"> <!-- col-auto my-1 -->
			    <label class="mr-sm-2 sr-only" for="inlineFormCustomSelect">Preference</label>
				<select class="custom-select mr-sm-2" id="inlineFormCustomSelect"
					name="selt1">
					<option selected value=""><%=(selt1==null? "选择":selt1)%></option>
					<option value="and">and</option>
					<option value="or">or</option>
				</select>
		     </div>
				<div class="input-group flex-nowrap">
				<div class="input-group-prepend">
						<span class="input-group-text" id="inputGroup-sizing-default">打卡次数小于</span>
					</div>
				 <input type="number" class="form-control" name="checknum" style="with:20px;" min="0"
					value="<%=checknum==null ? "":checknum %>"
					aria-label="Sizing example input"
					aria-describedby="inputGroup-sizing-default">
			  </div>
			 
			  <div class="col-auto my-1">
			    <label class="mr-sm-2 sr-only" for="inlineFormCustomSelect">Preference</label>
				<select class="custom-select mr-sm-2" id="inlineFormCustomSelect"
					name="selt2">
					<option selected value=""><%=(selt2==null? "选择":selt2)%></option>
					<option value="and">and</option>
					<option value="or">or</option>
					
				</select>
		     </div>
				<div class="input-group flex-nowrap">
				<div class="input-group-prepend">
						<span class="input-group-text" id="inputGroup-sizing-default">核酸检测次数小于</span>
					</div> 
				 <input type="number" class="form-control" name="testnum" style="with:20px;" min="0" oninput="value=value.replace(/[^\d]/g,'')"
					value="<%=testnum==null ? "":testnum %>"
					aria-label="Sizing example input"
					aria-describedby="inputGroup-sizing-default">
			  </div>
			  
		    <div class="">
				<button type="submit" class="btn btn-primary">查询</button>
			</div>
			
		</div>
		
		 
				
	</form>

<hr>

<% 
if(datetime!=null&& datetime.length()>5){ 
	date1=datetime.split("T")[0]+" "+datetime.split("T")[1];
	date2=datetime2.split("T")[0]+" "+datetime.split("T")[1];
	
  if(testnum!=null && checknum!=null && selt1.length()>0 && selt2.length()>0){ 
	 
	/*  sql="SELECT a.TeaId ,TeaName,DepName ,num, IFNULL( num2,0) AS num2 FROM"+
			 "( SELECT TeaId , COUNT(TeaId) AS num , TeaName,DepName from teastatistics DATE >='"+date1+"' and  Date <='"+date2+"' and DepName='"+DepName+"' and IsTested like '%"+"已完成"+"%' GROUP BY TeaId )AS a "+
			 "left join (SELECT TeaId,COUNT(TeaId) AS num2  from teastatistics WHERE IsTested='已完成' GROUP BY TeaId ) AS b "+
			 "ON  a.TeaId=b.TeaId HAVING num<4 AND num2<3";  */
	 		 
	 sql="SELECT a.TeaId ,a.TeaName,a.DepName ,num ,ifnull(num1,0) AS num1 , ifnull(num2,0) as num2 FROM"+
	 "(SELECT TeaId,COUNT(TeaId) AS num, TeaName,DepName FROM teastatistics where  DATE >='"+date1+"' and  Date <='"+date2+"' and DepName='"+DepName+"' GROUP BY TeaId ) AS a LEFT join"+
			 "( SELECT TeaId , COUNT(TeaId) AS num1 , TeaName,DepName from teastatistics WHERE  DATE >='"+date1+"' and  Date <='"+date2+"' and DepName='"+DepName+"' and IsCheck='已完成' GROUP BY TeaId )"+
	 " AS b ON  a.TeaId=b.TeaId left join (SELECT TeaId,COUNT(TeaId) AS num2  from teastatistics WHERE  DATE >='"+date1+"' and  Date <='"+date2+"' and DepName='"+DepName+"'and IsTested='已完成' "+
			 "GROUP BY TeaId ) AS c ON  b.TeaId=c.TeaId HAVING num1<'"+checknum+"' "+selt2+" num2<'"+testnum+"' ";
	 	System.out.println("sql"+sql); 
	 rs =new DBtools().query(sql);
	 
			 		 
	// sql="SELECT TeaId , COUNT(TeaId) AS num , TeaName,DepName from teastatistics WHERE DATE >='"+date1+"' and  Date <='"+date2+"' and DepName='"+DepName+"' and IsTested like '%"+"已完成"+"%' GROUP BY TeaId HAVING num <'"+testnum+"'";			 
  }
  else if(checknum!=null && selt1.length()>0){
	

	  
  }
 else if(testnum!=null &&  selt2.length()>0){
	 
 } 
 else{
//System.out.print("datetime="+date);
 //sql="select * from teastatistics where( Date >='"+date1+"') and ( Date <='"+date2+"') and DepName='"+DepName+"' "+
//"order by Date  desc  ";//尚未完善，更改	
	
sql="SELECT TeaId , COUNT(TeaId) AS num , TeaName,DepName from teastatistics WHERE DATE >='"+date1+"' and  Date <='"+date2+"' and DepName='"+DepName+"' and IsTested like '%"+"已完成"+"%' GROUP BY TeaId HAVING num <'"+testnum+"'";	
 }
  
  %>
	 <table width="80%" align="center" class="table table-striped">
	 <tr align="center" >
	            <th>教师工号</th>
	            <th>教师姓名</th> 
	            <th>所属学院</th>      
	            <th>核查天数</th>      
	            <th>打卡次数</th>      
	        	<th>核酸检测次数</th> 	   
	        </tr>
	    
	<%while( rs!=null&&rs.next() ){
		String TeaId = rs.getString("TeaId");
	%>
	<tr align="center" >	
		<td ><%=rs.getString("TeaId")%> </td>
		<td> <%=rs.getString("TeaName")%> </td>
		<td> <%=rs.getString("DepName")%></td>
		<td> <%=rs.getString("num")%></td>		 
		<td> <%=rs.getString("num1")%></td>
		<td> <%=rs.getString("num2")%></td>
	      <%}%>
	</tr>
	</table>		 
	<%		  
  
}




/* if(testnum!=null){
	sql="SELECT TeaId , COUNT(TeaId) AS num , TeaName,DepName from teastatistics WHERE DATE >= GROUP BY TeaId HAVING num>=1 ";
} */


//如果有输入内容则构造查询你条件
if(datetime!=null&& datetime.length()>0){	
	 rs =new DBtools().query(sql);
}
out.println("sql="+sql+"<br>");
System.out.println("sql="+sql+"<br>");
%>



<table width="80%" align="center" class="table table-striped">
 <tr align="center" >
            <th>教师工号</th>
            <th>教师姓名</th> 
            <th>所属学院</th>      
          <% if(checknum!=null){ %><th>核酸检测次数</th><%} 
          else {%><th>日期</th> 
            
            <th>打卡情况</th>
            <th>核酸检测</th>
            <th>异常备注</th>
            <%} %>
        </tr>
    
<%while( rs!=null&&rs.next() ){
	String TeaId = rs.getString("TeaId");
%>
<tr align="center" >	
	<td ><%=rs.getString("TeaId")%>  </td>
	<td> <%=rs.getString("TeaName")%> </td>
	<td> <%=rs.getString("DepName")%></td>
	 <% if(testnum!=null) {%><td> <%=rs.getString("num")%></td>
	 <%}else{ %>
	<td> <%=rs.getString("Date")%></td>
	<td> <%=rs.getString("IsCheck")%>  </td>
	<td> <%=rs.getString("IsTested")%>  </td>
	<td><%=rs.getString("IsUnusual")%></td>
      <%} %>
</tr>
<%}%>
</table>




</body>
</html>